﻿--drop database [OnlineExam];
--go

--create database [OnlineExam];
--go

--use [OnlineExam];
--go

-- 用户
create table [dbo].[Users] (
    [ID] [int] not null identity,
	[UID] [nvarchar](50) not null,
    [Name] [nvarchar](50) not null,
    [Salt] [nvarchar](256) not null,
    [Email] [nvarchar](256) not null,
	[HashedPassword] [nvarchar](256) not null,
    [Status] [tinyint] not null,
    [CreatedAt] [datetime] not null,
    [UpdatedAt] [datetime] not null,
    primary key ([ID])
);

create table [dbo].[UserMetas] (
    [ID] [int] not null identity,
	[UserID] [int] not null,
    [MetaKey] [nvarchar](256) not null,
    [MetaValue] [nvarchar](max) not null,
    primary key ([ID])
);

alter table [dbo].[UserMetas] 
add constraint [UserMeta_User] 
foreign key ([UserID]) 
references [dbo].[Users]([ID]) on delete cascade;

create table [dbo].[Categories] (
    [ID] [int] not null identity,
	[Taxonomy] [nvarchar](50) not null,-- posts,pages,links
    [Name] [nvarchar](50) not null,
	[ParentID] [int] not null,
    primary key ([ID])
);

-- 评论或留言
create table [dbo].[Comments] (
    [ID] [int] not null identity,
	[Target] [nvarchar](50) not null,
    [TargetID] [int] not null,
    [Name] [nvarchar](50) not null,
    [Email] [nvarchar](256) not null,
    [IP] [nvarchar](50) not null,
    [ParentID] [int] not null,
    [Body] [nvarchar](max) not null,
    [Status] [bit] not null,
    [CreatedAt] [datetime] not null,
    [UpdatedAt] [datetime] not null,
    primary key ([ID])
);

-- 友情链接
-- 菜单,排序和顺序
create table [dbo].[Links] (
    [ID] [int] not null identity,
    [Url] [nvarchar](256) not null,
    [Name] [nvarchar](256) not null,
    [Description] [nvarchar](256) not null,
    [CategoryID] [int] not null,
    primary key ([ID])
);

-- 文章:计数，审核
create table [dbo].[Posts] (
    [ID] [int] not null identity,
    [Title] [nvarchar](256) not null,
	[Clicks] [int] not null,
    [UserID] [int] not null,
    [CategoryID] [int] not null,
    [Body] [nvarchar](max) not null,
	[Status] [bit] not null,
    [CreatedAt] [datetime] not null,
    [UpdatedAt] [datetime] not null,
    primary key ([ID])
);

-- 页面，专题，新闻通知,焦点新闻
create table [dbo].[PostMetas] (
    [ID] [int] not null identity,
    [PostID] [int] not null,
    [MetaKey] [nvarchar](256) not null,
    [MetaValue] [nvarchar](max) not null,
    primary key ([ID])
);

-- 系统元数据:主题元素，配置文件等
create table [dbo].[MetaDatas] (
    [ID] [int] not null identity,
	[Family] [nvarchar](50) not null,
    [MetaKey] [nvarchar](256) not null,
    [MetaValue] [nvarchar](max) not null,
    primary key ([ID])
);

-- 主键(一个)-外键(多个)

-- 关联用户和组
alter table [dbo].[Links] add constraint [Link_Category] foreign key ([CategoryID]) references [dbo].[Categories]([ID]) on delete cascade;
alter table [dbo].[Posts] add constraint [Post_Category] foreign key ([CategoryID]) references [dbo].[Categories]([ID]) on delete cascade;
alter table [dbo].[Posts] add constraint [Post_User] foreign key ([UserID]) references [dbo].[Users]([ID]) on delete cascade;
alter table [dbo].[PostMetas] add constraint [PostMeta_Post] foreign key ([PostID]) references [dbo].[Posts]([ID]) on delete cascade;

-- 问题
create table [dbo].[Problems] (
    [ID] [int] not null identity,
	[CategoryID] [int] not null,
	[Body] [nvarchar](max) not null,
	[Type] [tinyint] not null,
    [CreatedAt] [datetime] not null,
    [UpdatedAt] [datetime] not null,
    primary key ([ID])
);

alter table [dbo].[Problems] add constraint [Problem_Category] foreign key ([CategoryID]) references [dbo].[Categories]([ID]) on delete cascade;

create table [dbo].[ProblemMetas] (
    [ID] [int] not null identity,
	[ProblemID] [int] not null,
    [MetaKey] [nvarchar](256) not null,
    [MetaValue] [nvarchar](max) not null,
    primary key ([ID])
);

alter table [dbo].[ProblemMetas] 
add constraint [ProblemMeta_Problem] 
foreign key ([ProblemID]) 
references [dbo].[Problems]([ID]) on delete cascade;

-- 试卷

create table [dbo].[Papers] (
    [ID] [int] not null identity,
	[CategoryID] [int] not null,
	[Name] [nvarchar](256) not null,
    [CreatedAt] [datetime] not null,
    [UpdatedAt] [datetime] not null,
    primary key ([ID])
);

alter table [dbo].[Papers] add constraint [Paper_Category] foreign key ([CategoryID]) references [dbo].[Categories]([ID]) on delete cascade;

create table [dbo].[PaperMetas] (
    [ID] [int] not null identity,
	[PaperID] [int] not null,
    [MetaKey] [nvarchar](256) not null,
    [MetaValue] [nvarchar](max) not null,
    primary key ([ID])
);

alter table [dbo].[PaperMetas] 
add constraint [PaperMeta_Paper] 
foreign key ([PaperID]) 
references [dbo].[Papers]([ID]) on delete cascade;

-- 考试

create table [dbo].[Exams] (
    [ID] [int] not null identity,
	[CategoryID] [int] not null,
	[Address] [nvarchar](256) not null,
	[DateTime] [datetime] not null,
	[Syllabus] [nvarchar](max) not null,
	[Notes] [nvarchar](max) not null,
    [CreatedAt] [datetime] not null,
    [UpdatedAt] [datetime] not null,
    primary key ([ID])
);

alter table [dbo].[Exams] add constraint [Exam_Category] foreign key ([CategoryID]) references [dbo].[Categories]([ID]) on delete cascade;

create table [dbo].[ExamMetas] (
    [ID] [int] not null identity,
	[ExamID] [int] not null,
    [MetaKey] [nvarchar](256) not null,
    [MetaValue] [nvarchar](max) not null,
    primary key ([ID])
);

alter table [dbo].[ExamMetas] 
add constraint [ExamMeta_Exam] 
foreign key ([ExamID]) 
references [dbo].[Exams]([ID]) on delete cascade;

-- 报名表

create table [dbo].[Attendances] (
    [ID] [int] not null identity,
	[ExamID] [int] not null,
	[UserID] [int] not null,
	[StartedAt] [datetime] not null,
	[EndedAt] [datetime] not null,
	[Status] [tinyint] not null,
	[Score] [nvarchar](max) not null,
    [CreatedAt] [datetime] not null,
    [UpdatedAt] [datetime] not null,
    primary key ([ID])
);

alter table [dbo].[Attendances] 
add constraint [Attendance_Exam] 
foreign key ([ExamID]) 
references [dbo].[Exams]([ID]) on delete cascade;

alter table [dbo].[Attendances] 
add constraint [Attendance_User] 
foreign key ([UserID]) 
references [dbo].[Users]([ID]) on delete cascade;

-- ACL
